The prior art limits its application to a single function (either data conversion or, to a limited extent, data migration among similar databases). There is no prior art defined that is generic enough to cover multiple applications and yet specific enough to allow for tasks such as consolidation, restoration of archived data, and changing configurations. The majority of the prior work only deals with the conversion of data.
There are generally four methods for converting data. The first method, and most traditional approach, begins by extracting data from an original system (source) to an ASCII file. The second step in the approach is to create a script (usually written in SQL) and use a program (such as SQL*Loader.TM.) to populate an Oracle table (destination). The destination table may be either a table or an application programmatic interface which is effectively a standard intermediate table provided by Oracle which performs an update routine to load data into the final application tables. FIG. 1 illustrates the traditional conversion process. The SQL code is used to map the data into the appropriate field in the destination database and to translate the data from the source format to the destination format. In addition, a programmer might add required fields, enforce any data dependencies (e.g. if the value in field 1 is A, then the value in field 2 must be B) and validate the data (e.g. if a unit of measure for an item is dozen, then the value dozen must be present in a related table or list of values as a valid unit of measure). This code is usually extremely complex to write and maintain. Appendix A shows a sample portion of a SQL*Loader script that might be used to load vendor information. Appendix B is an extract of sample SQL code used to check for data dependencies prior to loading accounts receivable data. A typical company will have more than 100 of these SQL scripts to code and maintain to convert their data for just financial applications.
A second method to load data is to cut and paste that data from a spreadsheet. Records copied from a spreadsheet and using a Microsoft Windows.RTM. paste function are inserted into an Oracle table. Subsequent manipulation of the data similar to that of the traditional method (FIG. 1) is performed by a programmer using SQL code to add additional required fields, validate the data, and enforce dependencies. Again, an intermediate table or application programmatic interfaces may be used prior to loading the data into the final application tables.
The third method of converting data is to utilize a tool to populate Oracle tables. The tools currently on the market generate the SQL code to map source fields to destination fields. Some of these tools allow for adding required fields or check for data validation or enforce data dependencies, but these checks are hard-coded into the SQL script for specified tables. Very few of the tools enforce referential integrity and maintain foreign key relationships. Prior art utilizes programs (generally in SQL) to move data from an old database to a new one. These tools generate the SQL code interactively (e.g. table by table, and, in many cases, instruction by instruction).
The final method is to manually enter data into the new application tables usually from a print out or report of the source data.
The following table indicates products of different companies, their functionality and their limitations:
______________________________________ Company Product Functionality Limitations ______________________________________ SMART Smart DB Transforms and Performs no other function Corp. maps legacy except conversion, and then data to new only translates and application. transforms the values of the Generates data. and executes The product maps table to SQL code to table, rather than insert, update, understanding the or delete relationships in the entire data. Graphi- application. It can only cally joins accommodate data that tables and originates in one table with views. Manipu- a single destination table. lates data val- Does not enforce referential ues among integrity, data dependen- multiple tables. cies, uniqueness con- straints. Does not provide patterns, pre-defined migra- tion rules. Audit Trail not complete enough to reverse transactions. Some pre-defined templates are provided for Oracle Applications .TM.. Requires recoding, and new template definition for new or updated versions of the destination tables. Constellar Constellar Ongoing moving Requires expert knowledge Corp. Hub of transaction of programming languages and operational (Cobol, SQL, C) and data to and from Oracle7 .RTM. architecture. legacy systems Does not appear to enforce into databases referential integrity, data (Oracle, DB/2, dependencies, uniqueness Informix, IDMS) constraints. Performs no and files. Popu- functions other than lating and refresh- conversion and data ing of data ware- mining. Does not provide houses. Generates patterns, pre-defined migra- C, Cobol and tion rules. Requires recod- Pro*C source ing for new or updated ver- code. Uses Trans- sions of the destination formation Defini- tables. Does not come tion Language to seeded with data particular create business to Oracle Applications .TM., rules to filter or with predefined migra- data. Supports tion rules. data dictionary and CASE reposi- tories in the transformation of data. Constantly sits on network and manages data flow. Oracle Data Stores data in a DCT has no drivers or Corp. Conversion central repository. control files specific to Tool (part of Manages data Oracle Applications .TM.. Enterprise conversions by DCT has no predefined Data generating code rules for migrating data and Management to map data maintaining relational and System) from legacy data integrity. The user (DCT) systems to must specify the order of Oracle .RTM. data- the conversion execution. bases or from DCT alone does not enforce Oracle to a flat referential integrity, data file. DCT mines dependencies, or unique- transactions for ness constraints. data warehouse DCT requires extensive and data marts programming expertise in and performs an SQL and PL/SQL to define actual extract of the conditions of data legacy data. Uses transfer, and to create the gateways for non- load packages. Oracle systems to DCT requires knowledge of create extracts of relational database legacy data. DCT architecture and the design can convert flat of the destination system, files to relational and knowledge of the tables, or tables to SQL*Loader utility's flat files. concepts and features to define the migration process. Performs no other function except conversion. Does not provide patterns, pre-defined migration rules. Only supports conversion to a single destination table for each result. In other words, for a source to load more than one destination table would require the user to define separate conver- sion groups and create sep- arate SQL scripts. Does not support one-to-one or many-to-one transforma- tions within a single con- version group. The DCT user must write and test his own SQL scripts and PL/SQL packages for transformation of the data. The user must also create the required cursors for each group. The user creates a script that executes the stored procedures and passes the needed parameters to schedule and control the migration process. For a file based conversion, the control script must also FTP the files to the desti- nation system and then exe- cute a SQL*Loader process to perform the data load. Chain Chain Link Manages multiple Only migrates a small set of Link Object instances of the Oracle Application Object Tech- Migrator Application Ob- Library .TM. tables. nologies, Chain Link ject Library .TM. Performs no other function Inc. Migration tables across mul- except migration to differ- Manager tiple Oracle .RTM. ent database instances. RDBMS environ- Does not provide patterns, ments (produc- pre-defined migration rules, tion, test, deve- translation, or lopment, etc.) transformation of data. Migrates data Requires recoding for new from Application or updated versions of the Object Library .TM. destination tables. tables to different Oracle database instances. Rockport Conversion The Conversion Conversion Toolbox does Software, Toolbox Toolbox performs not provide for generation Inc. conversion of of data translation or trans- data from a formation instructions. It legacy system in- allows a user to change val- to selected Oracle ues (e.g. from doz. to doz- Applications .TM. en), but does not provide tables. The pro- any rules or conditionals for duct also claims doing so. The user merely to be able to sup- indicates the new value port two way in- (field-by-field) to be moved terfaces, and sup- to the destination tables. port changes to The mapping facility of the the accounting Conversion Toolbox is lim- flexfield, al- ited and allows only for sin- though the latter gle source to a single desti- functionality was nation table translation. The not complete in actual values in the source the version of the are not displayed on the software evalu- screen within the software. ated. The soft- The user must define the ware dynamically translated values based on generates driver prior knowledge of the leg- tables to migrate acy system's data. The the data and vali- Conversion Toolbox can date data depen- only be used for convers dencies. It allows ions and table-to-table map- for translation of ping. Conversion Toolbox values and addi- has no predefined tem- tion of new re- plates. Does not provide quired data to in- patterns, predefined migra- termediate tables. tion rules. Audit Trail not These driver complete enough to reverse tables provide transactions. Does not en- generic migration force referential integrity, instructions to uniqueness constraints the toolbox. In without significant user other words, the input. Performs no other Conversion Tool- function except conversion. box allows the user to create templates to gen- erate the migra- tion instructions. These migration instructions use predefined driver tables to move the data from the source to the des- tination. The Conversion Tool- box uses a single intermediate table to translate and transform the data. This use of a single table limits the migra- tion to a single destination table and makes the en- forcement of ref- erential integrity depend on the user's knowledge of the precise migration order that must be fol- lowed to move the data. Using the Conversion Toolbox, the user might have to define thousands of templates to migrate an entire application and keep track of the order of migration in order to ensure that all of the templates are applied in the proper sequence. ______________________________________
U.S. Pat. No. 5,596,746 discloses a method and system to transform relational data models into object models (input and output to modeling tool). The invention has the following features:
User must have a database design; PA1 Generates SQL Code; PA1 Uses graphic depiction of entity relationship model rather than the unique identifier; PA1 Creates data dictionary; PA1 Stores tables and data models rather than dynamically creating insert records; PA1 Creates the data model rather than populating the tables with new data; PA1 User needs to input primary keys; PA1 Populate refers to insertion of object name (such as table and column names) rather than load data. PA1 a) Transfer Mapping; PA1 b) Monovariate Translation; PA1 c) Multivariate Concatenation Translation; PA1 d) Multivariate Reverse Concatenation Translation; and PA1 e) Multivariate Transformation. PA1 f) Parsing, combining, adding to, or changing the format or content of the source data. PA1 g) Specification of constraints or conditions under which these mapping patterns apply. PA1 h) Association of source data with the format, content, and characteristics of the destination data. PA1 i) Analysis of the patterns and conditionals defined for logical application to the source data. PA1 a) Validates that mandatory fields are populated. PA1 b) Adds additional data required by the destination. PA1 c) Determines where to put source data that doesn't have a corresponding field in the destination. PA1 a) Rules are based on pre-defined hierarchies or nesting of relationship in the destination system. PA1 b) These rules control the migration process. PA1 a) Definition of hierarchies of templates based on principles of relational database design. PA1 b) Grouping of templates together to move a particular type or types of data. PA1 c) Iterative processing of the mapping patterns until all of the source data has been transferred, translated, or transformed. PA1 a) Addition of control fields to batches to maintain audit history of transaction processing. PA1 b) Ability to schedule the running of batches and specify the order of processing. PA1 a) Checks uniqueness constraints. PA1 b) Validates population of mandatory fields in the destination system. PA1 c) Confirms that data dependency requirements are satisfied. PA1 d) Enforces referential integrity. PA1 e) Maintains a logical order of operations in application of templates and rules. PA1 a) A transaction report identifies errors. PA1 b) The transaction report notes errors that fail uniqueness, data validation and violate dependency rules. PA1 c) No records containing errors are written to destination tables. PA1 d) Individual or mass corrections. PA1 e) A change history of corrections is made so that the data in the destination remains consistent with that in the source and an audit trail is maintained. PA1 f) The entire migration process can be reversed so that the destination data remains intact and in the same condition as before the migration took place. PA1 Allow for the consolidation of data into a single destination system from multiple source systems. PA1 Include changing or modifying the content, format, or characteristics of data as business requirements or systems change. PA1 Restore archived data to a current database. PA1 Migrate data from one database instance or installation to another. PA1 Integrate enhancements, bolt-ons, and modifications to a relational application. PA1 Convert data from a legacy system to a new system. PA1 Consolidate multiple installations of a subledger into a multiorg environment. PA1 Support the consolidation of systems to support mergers or acquisitions. PA1 Add additional data or populate multiple tables simultaneously. PA1 Migrate data from a development, to a test, to a training, or to a production environment, without having to reconfigure the setup parameters each time. PA1 Allow for definition of data dependencies in the destination. PA1 Allow for definition of primary keys, check constraints, and uniqueness in the destination that maintain constraints to ensure the data integrity of the destination tables. PA1 Allow for definition of relationships and foreign keys in the destination that, in turn, allow for maintaining referential integrity. PA1 Allow for assigning order of operations to the processing of templates by using level coding algorithms for relationships that identify those destination tables with the fewest relationships to populate first, and progressing sequentially through the more complex relationships. PA1 Provide for definition of seeded data that identifies data and referential dependencies within Oracle Applications.TM.. PA1 Allow the characteristics of every Oracle Applications.TM. table to be pre-defined.
U.S. Pat. Nos. 5,566,332; 5,416,917; and 5,278,978 disclose inventions which refer to transferring data between dissimilar hardware environments. The transfer is only between two relational database management systems. They deal with the types of storage on different machines (e.g. whether numeric data is physically stored in a low to high order sequence or a high to low sequence). They are based on machine and system descriptors to establish links between two relational database systems.
U.S. Pat. No. 5,379,419 discloses a method and system to decode relational queries into a set of common data file commands to retrieve data in non-relational formats.